﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Transactions;

namespace LogicUniversityBiz
{
    public class EFFascade
    {
        LogicUniversityEntities contextDB = new LogicUniversityEntities();

        public static EFFascade getEFFascade()
        {
            return new EFFascade();
        }

        public string getEmailOf(string id)
        {
            var query = from e in contextDB.Employees
                        where e.Employee_ID == id
                        select e.Email;

            return query.SingleOrDefault();
        }

        public string getHeadEmail(string code)
        {
            var query = from e in contextDB.Employees
                        where e.Dept_Code == code && (e.Role == "Department Head" || e.Temp_Role=="Department Head")
                        select e.Email;

            return query.SingleOrDefault();
        }
        public string getEmpName(string id)
        {
            var query = from emp in contextDB.Employees
                        where emp.Employee_ID == id
                        select emp.Employee_Name;

            return query.SingleOrDefault();
        }

        public Employee getEmployeeInfo(string employeeID)
        {
            var query = from i in contextDB.Employees
                        where i.Employee_ID == employeeID
                        select i;
            return query.SingleOrDefault();
        }

        //to retrieve all catelogue
        public List<Catelogue> getAllCatelogue()
        {
            var query = (from c in contextDB.Catelogues
                         select c);

            return query.ToList();
        }

        public List<string> getAllCategory()
        {
            var query = (from c in contextDB.Catelogues
                         select c.Category).Distinct();

            return query.ToList();
        }

        public List<Catelogue> getCatelogue(string category)
        {
            var query = from c in contextDB.Catelogues
                        where c.Category == category
                        select c;

            return query.ToList();
        }

<<<<<<< .mine
        public Catelogue getCatelogueOf(string itemCode)
=======
        //to retrieve department code by empID
        public string getDepartmentCode(string empID)
>>>>>>> .r28
        {
            var query = from emp in contextDB.Employees
                        where emp.Employee_ID == empID
                        select emp.Dept_Code;

            return query.Single();
        }

        

        public List<Department> getOutstandingDepartmentNames()
        {
            var query = (from r in contextDB.Retrieval_List
                         join d in contextDB.Departments on r.Dept_Code equals d.Dept_Code
                         select d).Distinct();

            return query.ToList();
        }

        public List<OutstandingItems> getOustandingItems(string deptCode)
        {

            var query = from r in contextDB.Retrieval_List
                        join c in contextDB.Catelogues on r.Item_Code equals c.Item_Code
                        where r.Dept_Code == deptCode
                        group r by new {c.Item_Code,c.Item_Description,c.Measurement} into g
                        select new OutstandingItems()
                        {
                            ItemCode = g.Key.Item_Code,
                            ItemDesc = g.Key.Item_Description,
                            Measurement = g.Key.Measurement,
                            

                            //TotalRequested = (
                            //    from r2 in contextDB.Retrieval_List
                            //    join c2 in contextDB.Catelogues
                            //    on r2.Item_Code equals c2.Item_Code
                            //    select r2.Need_Quanty
                            //).Sum(),

                            TotalRequested=g.Sum(t1=>t1.Need_Quanty),

                            //TotalReceived = (
                            //    from r2 in contextDB.Retrieval_List
                            //    join c2 in contextDB.Catelogues
                            //    on r2.Item_Code equals c2.Item_Code
                            //    select r2.Actual_Quanty
                            //).Sum(),

                            TotalReceived=g.Sum(t2=>t2.Actual_Quanty),

                            //OutstandingQty = (
                            //    from r2 in contextDB.Retrieval_List
                            //    join c2 in contextDB.Catelogues
                            //    on r2.Item_Code equals c2.Item_Code
                            //    select r2.Need_Quanty
                            //).Sum() - (
                            //    from r2 in contextDB.Retrieval_List
                            //    join c2 in contextDB.Catelogues
                            //    on r2.Item_Code equals c2.Item_Code
                            //    select r2.Actual_Quanty
                            //).Sum()

                            OutstandingQty = g.Sum(t1 => t1.Need_Quanty) - g.Sum(t2 => t2.Actual_Quanty)
                        };
            //var query = from l in contextDB.Retrieval_List
            //            where l.Dept_Code == deptCode
            //            group new { l.Catelogue, l } by new
            //            {
            //                l.Catelogue.Item_Code,
            //                l.Catelogue.Item_Description,
            //                l.Dept_Code,
            //                l.Catelogue.Measurement
            //            } into g
            //            select new OutstandingItems()
            //            {
            //                g.Key.Item_Code,
            //                g.Key.Item_Description,
            //                Total_Requested_Qty = (System.Int32?)g.Sum(p => p.l.Need_Quanty),
            //                Total_Received_Qty = (System.Int32?)g.Sum(p => p.l.Actual_Quanty),
            //                Outstanding_Qty = (System.Int32?)(g.Sum(p => p.l.Need_Quanty) - g.Sum(p => p.l.Actual_Quanty)),
            //                g.Key.Measurement
            //            };

            return query.ToList();
        }

        public void insertRequisition(Requisition req)
        {


            contextDB.Requisitions.AddObject(req);
            contextDB.SaveChanges();

        }

        public void insertRequisitionDetail(Requisition_Detail reqDetail)
        {
            contextDB.Requisition_Detail.AddObject(reqDetail);
            contextDB.SaveChanges();
        }

        public int getLastRequisitionID()
        {
            var query = (from req in contextDB.Requisitions
                         select req.Requisition_ID).Max();

            return query;
        }

        //to show on MakeRequestDetail.aspx page
        public List<RequisitionWithItemDetails> getRequisitionItemsDetail(int reqID)
        {
            var query = from rd in contextDB.Requisition_Detail
                        join c in contextDB.Catelogues on rd.Item_Code equals c.Item_Code
                        where rd.Req_ID == reqID
                        select new RequisitionWithItemDetails()
                        {
                            ItemCode = c.Item_Code,
                            ItemDesc = c.Item_Description,
                            Quantity = rd.Quantity
                        };

            return query.ToList();
        }

        public List<RequisitionWithItemDetails> getTotalWeeklyRequisitions(DateTime fromDate, DateTime toDate, string deptCode)
        {
            var subquery = from r in contextDB.Requisitions
                           where r.Status.Equals("Approved") &&
                           (r.Approved_Date >= fromDate && r.Approved_Date <= toDate) &&
                           r.Dept_Code.Equals(deptCode)
                           select r.Requisition_ID;


            var query = from rd in contextDB.Requisition_Detail
                        join c in contextDB.Catelogues on rd.Item_Code equals c.Item_Code
                        where subquery.Contains((int)rd.Req_ID)
                        select new RequisitionWithItemDetails()
                        {
                            ItemCode = c.Item_Code,
                            ItemDesc = c.Item_Description,
                            Quantity = rd.Quantity
                        };

            return query.ToList();
        }

        public List<Catelogue> getCatelogueItemsUnderReorderLevel()
        {
            var query = (from c in contextDB.Catelogues
                         where c.Balance <= c.Reorder_Level
                         select c);

            return query.ToList();
        }

        public int getReorderQuantityOf(string itemCode)
        {
            var query = from c in contextDB.Catelogues
<<<<<<< .mine
                        where c.Item_Code == itemCode
                        select c;
=======
                        where c.Item_Code == itemCode
                        select c.Reorder_Qty;
>>>>>>> .r28

<<<<<<< .mine
            return query.Single();
        }

        //to retrieve department code by empID
        public string getDepartmentCode(string empID)
        {
            var query = from emp in contextDB.Employees
                        where emp.Employee_ID == empID
                        select emp.Dept_Code;

            return query.Single();
        }

        

        public List<Department> getOutstandingDepartmentNames()
        {
            var query = (from r in contextDB.Retrieval_List
                         join d in contextDB.Departments on r.Dept_Code equals d.Dept_Code
                         where r.Outstanding_Status==true
                         select d).Distinct();

=======
            return query.Single();
        }

        public int getCurrentBalanceOf(string itemCode)
        {
            var query = from c in contextDB.Catelogues
                        where c.Item_Code == itemCode
                        select c.Balance;

            return query.Single();
        }

        public decimal getUnitPriceOf(string itemcode)
        {
            var query = from td in contextDB.Tender_Detail
                        where td.Item_Code == itemcode
                        select td.Unit_Price;

            return query.Single();
        }

        public List<string> getSuppliersNames()
        {
            var query = from s in contextDB.Suppliers
                        select s.Supplier_Name;

>>>>>>> .r28
            return query.ToList();
        }
<<<<<<< .mine

        public List<OutstandingItems> getOustandingItems(string deptCode)
        {

            var query = from r in contextDB.Retrieval_List
                        join c in contextDB.Catelogues on r.Item_Code equals c.Item_Code
                        where r.Dept_Code == deptCode && r.Outstanding_Status==true
                        group r by new {c.Item_Code,c.Item_Description,c.Measurement} into g
                        select new OutstandingItems()
                        {
                            ItemCode = g.Key.Item_Code,
                            ItemDesc = g.Key.Item_Description,
                            Measurement = g.Key.Measurement,

                            TotalRequested=g.Sum(t1=>t1.Need_Quanty),

                            TotalReceived=g.Sum(t2=>t2.Actual_Quanty),

                            OutstandingQty = g.Sum(t1 => t1.Need_Quanty) - g.Sum(t2 => t2.Actual_Quanty)
                        };

            return query.ToList();
        }

        public void insertRequisition(Requisition req)
        {


            contextDB.Requisitions.AddObject(req);
            contextDB.SaveChanges();

        }

        public void insertRequisitionDetail(Requisition_Detail reqDetail)
        {
            contextDB.Requisition_Detail.AddObject(reqDetail);
            contextDB.SaveChanges();
        }

        public int getLastRequisitionID()
        {
            var query = (from req in contextDB.Requisitions
                         select req.Requisition_ID).Max();

            return query;
        }

        //to show on MakeRequestDetail.aspx page
        public List<RequisitionWithItemDetails> getRequisitionItemsDetail(int reqID)
        {
            var query = from rd in contextDB.Requisition_Detail
                        join c in contextDB.Catelogues on rd.Item_Code equals c.Item_Code
                        where rd.Req_ID == reqID
                        select new RequisitionWithItemDetails()
                        {
                            ItemCode = c.Item_Code,
                            ItemDesc = c.Item_Description,
                            Quantity = rd.Quantity
                        };

            return query.ToList();
        }

        public List<RequisitionWithItemDetails> getTotalWeeklyRequisitions(DateTime fromDate, DateTime toDate, string deptCode)
        {
            var subquery = from r in contextDB.Requisitions
                           where r.Status.Equals("Approved") &&
                           (r.Approved_Date >= fromDate && r.Approved_Date <= toDate) &&
                           r.Dept_Code.Equals(deptCode)
                           select r.Requisition_ID;


            var query = from rd in contextDB.Requisition_Detail
                        join c in contextDB.Catelogues on rd.Item_Code equals c.Item_Code
                        where subquery.Contains((int)rd.Req_ID)
                        select new RequisitionWithItemDetails()
                        {
                            ItemCode = c.Item_Code,
                            ItemDesc = c.Item_Description,
                            Quantity = rd.Quantity
                        };

            return query.ToList();
        }

        public List<Catelogue> getCatelogueItemsUnderReorderLevel()
        {
            var query = (from c in contextDB.Catelogues
                         where c.Balance <= c.Reorder_Level
                         select c);

            return query.ToList();
        }

        public int getReorderQuantityOf(string itemCode)
        {
            var query = from c in contextDB.Catelogues
                        where c.Item_Code == itemCode
                        select c.Reorder_Qty;

            return query.Single();
        }

        public int getCurrentBalanceOf(string itemCode)
        {
            var query = from c in contextDB.Catelogues
                        where c.Item_Code == itemCode
                        select c.Balance;

            return query.Single();
        }

        public decimal getUnitPriceOf(string itemcode)
        {
            var query = from td in contextDB.Tender_Detail
                        where td.Item_Code == itemcode
                        select td.Unit_Price;

            return query.Single();
        }

        public List<string> getSuppliersNames()
        {
            var query = from s in contextDB.Suppliers
                        select s.Supplier_Name;

            return query.ToList();
        }

        public string getSupplierIDBy(string name)
        {
            var query = from s in contextDB.Suppliers
                        where s.Supplier_Name == name
                        select s.Supplier_Code;

            return query.Single();
        }

        public List<Catelogue> getStationeryItemsSuppliedBy(string code)
        {
            
            var query = from c in contextDB.Catelogues
                        join td in contextDB.Tender_Detail on c.Item_Code equals td.Item_Code
                        join t in contextDB.Tenders on td.Tender_ID equals t.Tender_ID
                        where t.Supplier_ID == code
                        select c;

            return query.ToList();
        }

        public void recorePurchaseOrder(Purchse_Order purchaseOrder)
        {
            using (TransactionScope trans = new TransactionScope())
            {
                contextDB.Purchse_Order.AddObject(purchaseOrder);

                foreach (Purchase_Order_Detail pd in purchaseOrder.Purchase_Order_Detail)
                {
                    contextDB.Purchase_Order_Detail.AddObject(pd);
                }

                contextDB.SaveChanges();
                trans.Complete();
            }
        }

        public int getLastOrderID()
        {
            var query = (from s in contextDB.Purchse_Order
                         select s.PO_No).Max();

            return query;

        }

        public List<int> getPurchaseOrderNoNotDelievered()
        {
            var query = from p in contextDB.Purchse_Order
                        where p.IsDelievered == false
                        select p.PO_No;

            return query.ToList();
        }

        public List<RequisitionWithItemDetails> getOrderItemsDetails(int po)
        {
            var query = from pd in contextDB.Purchase_Order_Detail
                        join c in contextDB.Catelogues on pd.Item_Code equals c.Item_Code
                        where pd.PO_No == po
                        select new RequisitionWithItemDetails()
                        {
                            ItemCode = pd.Item_Code,
                            ItemDesc = c.Item_Description,
                            Quantity = pd.Quantity
                        };

            return query.ToList();
        }

        public void recordPurchaseOrderDelivery(Delivery_Order delivery)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                contextDB.Delivery_Order.AddObject(delivery);
                
                foreach(Delivery_Detail detail in delivery.Delivery_Detail)
                {
                    contextDB.Delivery_Detail.AddObject(detail);
                }

                contextDB.SaveChanges();
                scope.Complete();
            }
        }

        public string getSupplierIDBy(int po)
        {
            var query = (from d in contextDB.Delivery_Order
                        join p in contextDB.Purchse_Order on d.PO_No equals p.PO_No
                        where d.PO_No==po
                        select p.Supplier_ID).Distinct();

            return query.Single();
        }

        public int lastStockCardID()
        {
            var query = (from stock in contextDB.Stock_Card
                         select stock.StockCard_ID).Max();

            return query;
        }
        
        //to retrieve last stock id;
        public int getStockCardIdOf(string itemCode)
        {
            var query = from stock in contextDB.Stock_Card
                        where stock.Item_Code == itemCode
                        select stock.StockCard_ID;

            return query.Single();
        }
        public void updateStockCard(Delivery_Order delivery)
        {
            //updating Purchase Order IsDelivered
            var query = from p in contextDB.Purchse_Order
                        where p.PO_No == delivery.PO_No
                        select p;

            Purchse_Order purchase = (Purchse_Order)query.Single();
            purchase.IsDelievered = true;
            //updating IsDelievered End

            contextDB.SaveChanges();
            int stockID;

            foreach (Delivery_Detail d in delivery.Delivery_Detail)
            {
                string itemCode = d.Item_Code;

                var stockQuery = (from stock in contextDB.Stock_Card
                                 where stock.Item_Code == itemCode
                                 select stock.StockCard_ID).SingleOrDefault();

                if (stockQuery==0)
                {
                    Stock_Card stock = new Stock_Card();
                    stock.Item_Code = itemCode;
                    contextDB.Stock_Card.AddObject(stock);
                    contextDB.SaveChanges();

                    stockID = lastStockCardID();
                    recordStockDetail(d, stockID, delivery.Delivery_Date, delivery.PO_No, stock.Item_Code);
                }
                else
                {
                    stockID = stockQuery;
                    recordStockDetail(d, stockID, delivery.Delivery_Date, delivery.PO_No, itemCode);
                }

                
            }
        }

        public void recordStockDetail(Delivery_Detail detail,int stockID,DateTime deliveryDate,int po,string itemCode)
        {
            Stock_Card_Detail stockDetail = new Stock_Card_Detail();

            stockDetail.Date = deliveryDate;
            stockDetail.Quantity = +detail.Quantity;
            stockDetail.Description = "Supplier - " + getSupplierIDBy(po);
            stockDetail.Stock_Card_ID = stockID;
            stockDetail.Balance = getCurrentBalanceOf(itemCode) + stockDetail.Quantity;

            Catelogue cat = getCatelogueOf(itemCode);
            cat.Balance = stockDetail.Balance;

            contextDB.Stock_Card_Detail.AddObject(stockDetail);
            contextDB.SaveChanges();
        }

        public List<TotalRequestedItems> getTotalRequestedItemsOf(string deptCode)
        {
            var query = from retr in contextDB.Retrieval_List
                        join cat in contextDB.Catelogues
                        on retr.Item_Code equals cat.Item_Code
                        where retr.Dept_Code == deptCode && retr.Disbursement_Status == false
                        group retr by new
                        {
                            cat.Item_Code,
                            cat.Item_Description
                        } into g
                        select new TotalRequestedItems()
                        {
                            ItemCode = g.Key.Item_Code,
                            ItemDesc = g.Key.Item_Description,
                            NeedQty = g.Sum(t1 => t1.Need_Quanty),
                            ActualQty = g.Sum(t2 => t2.Actual_Quanty)
                        };

            return query.ToList();
        }

        public void acknowledgeRequestedItems(List<string> itemCodes, string empID,string deptCode)
        {
            foreach (string code in itemCodes)
            {
                var retr = (from r in contextDB.Retrieval_List
                           where r.Item_Code == code && r.Disbursement_Status == false && r.Dept_Code==deptCode
                           select r).SingleOrDefault();

                retr.Disbursement_Status = true;
                retr.Acknowledge_By = empID;

                contextDB.SaveChanges();
            }
        }
=======

        public string getSupplierIDBy(string name)
        {
            var query = from s in contextDB.Suppliers
                        where s.Supplier_Name == name
                        select s.Supplier_Code;

            return query.Single();
        }

        public List<Catelogue> getStationeryItemsSuppliedBy(string code)
        {
            var query = from c in contextDB.Catelogues
                        join td in contextDB.Tender_Detail on c.Item_Code equals td.Item_Code
                        join t in contextDB.Tenders on td.Tender_ID equals t.Tender_ID
                        where t.Supplier_ID == code
                        select c;

            return query.ToList();
        }

        public void recorePurchaseOrder(Purchse_Order purchaseOrder)
        {
            using (TransactionScope trans = new TransactionScope())
            {
                contextDB.Purchse_Order.AddObject(purchaseOrder);

                foreach (Purchase_Order_Detail pd in purchaseOrder.Purchase_Order_Detail)
                {
                    contextDB.Purchase_Order_Detail.AddObject(pd);
                }

                contextDB.SaveChanges();
                trans.Complete();
            }
        }
>>>>>>> .r28
    }
}
